﻿//using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;
using Office = Microsoft.Office.Core;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Drawing;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.Data.PivotGrid;
using DevExpress.Utils;

public partial class UserControl_ControlBaoCaoNgayTonKho : System.Web.UI.UserControl
{
    ClassExecute.ClassExecute dt;
    string str = webapp4U.Configuration.CurrentConnectionString;
    string error = "";
   
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            if (Session["db"] != null)
            {
                DataTable db = (DataTable)Session["db"];
                ASPxPivotGrid1.DataSource = db;
                ASPxPivotGrid1.DataBind();
            }
        }
        else
        {
            LoadMonthLy();
            
        }
    }
    void ShowMessageBox(string text)
    {
        MessageBox.Text = text;
        MessageBox.ShowOnPageLoad = true;
    }
    protected void imgBtnAdd_Click(object sender, ImageClickEventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        try
        {


            DataTable db = dt.Get_Data_DataTable(str, ref error, "[k_reportNgayTonKho]", CommandType.StoredProcedure,
                     new SqlParameter("@userName",Session["user"].ToString()),
                     new SqlParameter("@YyyyMm", DropMonthy.SelectedValue.ToString()));

            
            
            if (db.Rows.Count > 0)
            {

                ASPxPivotGrid1.DataSource = db;
                Session["db"] = db;
            }
            else
            {
                if (Session["db"] != null)
                {
                    Session["db"] = null;
                }
                ASPxPivotGrid1.DataSource = null;
                ShowMessageBox("Dữ liệu không có");
            }
            ASPxPivotGrid1.DataBind();
            //RadioButtonList1.SelectedValue = "0";
            //ArrangeReport();
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    protected void imgBtnSave_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            using (MemoryStream filestream = new MemoryStream())
            {
                ASPxPivotGridExporter1.OptionsPrint.PrintFilterHeaders = DefaultBoolean.False;
                ASPxPivotGridExporter1.OptionsPrint.PrintColumnHeaders = DefaultBoolean.True;
                ASPxPivotGridExporter1.OptionsPrint.PrintRowHeaders = DefaultBoolean.True;
                ASPxPivotGridExporter1.OptionsPrint.PrintDataHeaders = DefaultBoolean.False;

                string contentType = "application/ms-excel";

                string fileName = "BaoCaoNgayTonKho_"+ DropMonthy.SelectedValue.ToString()+"_" + DateTime.Now.ToString("yyyyMMdd") + ".xls";
                ASPxPivotGridExporter1.ExportToXls(filestream,false);
                byte[] buffer = filestream.GetBuffer();
                Response.Clear();
                Response.Buffer = false;
                Response.AppendHeader("Content-Type", contentType);
                Response.AppendHeader("Content-Transfer-Encoding", "binary");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                Response.BinaryWrite(buffer);
                Response.End();
            }
        }
        catch
        {

        }
    }
    void LoadMonthLy()
    {
        dt = new ClassExecute.ClassExecute();
        try
        {

            DataTable db = dt.Get_Data_DataTable(str, ref error, "k_getMonthly", CommandType.StoredProcedure);
            dt.BindDropDownList(db, DropMonthy, "ID", "Des");
            
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();
                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";
                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }

    protected void imgBtnPivot_Click(object sender, ImageClickEventArgs e)
        {
            generatePivotExcel();
        }
    
    //protected void ASPxPivotGrid1_CustomCellDisplayText(object sender, PivotCellDisplayTextEventArgs e)
    //    {
    //        if (object.ReferenceEquals(e.DataField, ASPxPivotGrid1.Fields["fInvMonth"]))
    //        {
    //            PivotGridField AvgSalesField = ASPxPivotGrid1.Fields["fAvgSales"];
    //            object AvgSales = e.GetCellValue(AvgSalesField);
                
    //            PivotGridField InvQtyField = ASPxPivotGrid1.Fields["fInvQty"];
    //            object InvQty = e.GetCellValue(InvQtyField);

    //            if ((AvgSales == null)||(InvQty==null) ) return;
    //            if ((decimal)AvgSales == 0) return;

    //            decimal dInvMonth = (decimal) InvQty/ (decimal)AvgSales;

    //            e.DisplayText = string.Format("{0:###.###,###}", dInvMonth);
    //        }


    //    }
    void generatePivotExcel()
        {

            string fileName = Session["user"].ToString() + "_BaoCaoNgayTonKho_" + DropMonthy.SelectedValue.ToString()  + ".xls";

            if (File.Exists(Server.MapPath(fileName)))
            {
                File.Delete(Server.MapPath(fileName));
            }


            string connection = @"OLEDB;Provider=SQLOLEDB.1;" + str;


            string cmd;
            cmd= "SELECT sitename, GAM, [Group], Kho, MatHang, AvgSales, InvQty FROM   dml_" + Session["user"].ToString() +"_vwInventoryDay";
            Excel.Application excel = new Excel.Application();
            try
            {
                excel.Visible = false;
                excel.AlertBeforeOverwriting = false;
                object[] noSub = { false, false, false, false, false, false, false, false, false, false, false, false };
                Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
                Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Excel.Style style = workbook.Styles.Add("Style1", Missing.Value);
                style.Font.Bold = true;
                style.Font.Size = 32;
                sheet.Name = "InvDay_" + DropMonthy.SelectedValue.ToString() ;
                sheet.Activate();

                foreach (Excel.Worksheet sh in workbook.Worksheets)
                {
                    if (sh.Name.ToString() != "InvDay_" + DropMonthy.SelectedValue.ToString() )
                    {
                        excel.DisplayAlerts = false;
                        sh.Delete();
                    }
                }

                Excel.PivotCache pivotCache = excel.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, Missing.Value);

                pivotCache.Connection = connection;
                pivotCache.MaintainConnection = true;




                pivotCache.CommandText = cmd;
                pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
                Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Missing.Value);
                Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, excel.ActiveCell[11, 2], "PivotTable11", Missing.Value, Missing.Value);

                // sitename, GAM, [Group], Kho, MatHang, AvgSales, InvQty

                Excel.PivotField pageField1 = (Excel.PivotField)pivotTable.PivotFields("SiteName");
                pageField1.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField1.Caption = "Nhà phân phối";
                pageField1.set_Subtotals(1, noSub);

                Excel.PivotField pageField2 = (Excel.PivotField)pivotTable.PivotFields("GAM");
                pageField2.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField2.Caption = "GAM";
                pageField2.set_Subtotals(1, noSub);

                Excel.PivotField pageField3 = (Excel.PivotField)pivotTable.PivotFields("Group");
                pageField3.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField3.Caption = "Group";
                pageField3.set_Subtotals(1, noSub);


                Excel.PivotField pageField4 = (Excel.PivotField)pivotTable.PivotFields("Kho");
                pageField4.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField4.Caption = "Kho";
                pageField4.set_Subtotals(1, noSub);


                Excel.PivotField pageField5 = (Excel.PivotField)pivotTable.PivotFields("MatHang");
                pageField5.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                pageField5.Caption = "Mặt hàng";
                pageField5.set_Subtotals(1, noSub);
                //AvgSales, InvQty
                pivotTable.AddDataField(pivotTable.PivotFields("AvgSales"), "Doanh số trung bình tháng", Excel.XlConsolidationFunction.xlSum);

                pivotTable.AddDataField(pivotTable.PivotFields("InvQty"), "Tồn kho", Excel.XlConsolidationFunction.xlSum);
                
                Excel.CalculatedFields cActRate = pivotTable.CalculatedFields();
                cActRate.Add("ThangTonKho", "= iferror('InvQty'/'AvgSales',0)", Missing.Value);
                try
                {
                    pivotTable.AddDataField((Excel.PivotField)cActRate, Missing.Value, Missing.Value);
                }
                catch { }
                pivotTable.AddDataField(pivotTable.PivotFields("ThangTonKho"), "Tháng tồn kho", Excel.XlConsolidationFunction.xlSum);


                pivotTable.RowGrand = false;

                pivotTable.DataPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

                Excel.Range rg = pivotTable.DataBodyRange;
                rg.NumberFormat = "#,##0.0#";

                sheet.Cells.EntireColumn.AutoFit();

                Excel.Range range = ((Excel.Range)sheet.Cells[1, 1]);
                range.Style = style;
                range.Value2 = "BÁO CÁO THÁNG TỒN KHO";

                Excel.Range range2 = ((Excel.Range)sheet.Cells[2, 1]);
                range2.Value2 = "Tính doanh số trung bình đến tháng " + DropMonthy.Text.ToString() ;

                Excel.Range range1 = ((Excel.Range)sheet.Cells[3, 1]);
                range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();



                workbook.SaveAs(Server.MapPath(fileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                workbook.Close(true, Missing.Value, Missing.Value);
                excel.Quit();

                saveFile(this.Response, Server.MapPath(fileName));

            }
            catch
            {

            }
            finally
            {
                // Make sure we release the reference to the underlying COM object
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
        }
    protected void imgBtnClearFilter_Click(object sender, ImageClickEventArgs e)
        {
            for (int i = 0; i < ASPxPivotGrid1.Fields.Count; i++)
            {
                ASPxPivotGrid1.Fields[i].FilterValues.Clear();
            }
        }


}


